-- 检查存储过程是否存在
SELECT proname FROM pg_proc WHERE proname = 'redeem_reward';

-- 如果不存在，手动创建
CREATE OR REPLACE PROCEDURE redeem_reward(
    p_userid BIGINT,
    p_reward_id BIGINT,
    p_quantity INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
v_required_points INTEGER;
    v_current_points INTEGER;
    v_current_stock INTEGER;
    v_reward_name VARCHAR(100);
    v_total_points_required INTEGER;
BEGIN
    -- 1. 检查奖品是否存在并获取信息
SELECT points_required, stock, name
INTO v_required_points, v_current_stock, v_reward_name
FROM rewards
WHERE reward_id = p_reward_id;

IF v_required_points IS NULL THEN
        RAISE EXCEPTION '奖品不存在';
END IF;

    -- 2. 检查库存
    IF v_current_stock < p_quantity THEN
        RAISE EXCEPTION '库存不足';
END IF;

    -- 3. 计算总所需积分
    v_total_points_required := v_required_points * p_quantity;

    -- 4. 检查用户积分
SELECT points INTO v_current_points
FROM points_rewards
WHERE userid = p_userid;

IF v_current_points IS NULL THEN
        RAISE EXCEPTION '用户积分账户不存在';
END IF;

    IF v_current_points < v_total_points_required THEN
        RAISE EXCEPTION '积分不足';
END IF;

    -- 5. 扣除积分
UPDATE points_rewards
SET points = points - v_total_points_required,
    updated_at = NOW()
WHERE userid = p_userid;

-- 6. 减少库存
UPDATE rewards
SET stock = stock - p_quantity,
    updated_at = NOW()
WHERE reward_id = p_reward_id;

-- 7. 插入积分记录
INSERT INTO point_records (
    userid, points_change, source_type, related_id, quantity, description, created_at
) VALUES (
             p_userid,
             -v_total_points_required,
             'REWARD_REDEMPTION',
             p_reward_id,
             p_quantity,
             '兑换奖品：' || v_reward_name,
             NOW()
         );

END;
$$;